package net.wanho.commons.util;


import net.wanho.commons.exception.DataAccessException;
import org.apache.commons.beanutils.BeanUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.MapHandler;
import org.apache.commons.dbutils.handlers.MapListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;
import org.apache.commons.lang3.ArrayUtils;
import org.apache.log4j.Logger;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;


public class JDBCTemplate {
    private static final Logger LOGGER = Logger.getLogger(JDBCTemplate.class);

    public int insert(String sql, Object... params) throws DataAccessException {
        try {
            LOGGER.debug("执行插入的SQL==================>" + sql);
            LOGGER.debug("执行插入SQL参数================>" + ArrayUtils.toString(params));
            return new QueryRunner().insert(JDBCUtil.getConnection(), sql, new ScalarHandler<Long>(), params).intValue();
        } catch (SQLException e) {
            e.printStackTrace();
            LOGGER.error("执行插入异常:" + e.getMessage());
            throw new DataAccessException("数据保存异常");
        }
    }

    public boolean updateBatch(String sql, Object[]... params) throws DataAccessException {
        try {
            LOGGER.debug("执行修改的SQL==================>" + sql);
            LOGGER.debug("执行修改SQL参数================>" + ArrayUtils.toString(params));
            new QueryRunner().batch(JDBCUtil.getConnection(), sql, params);
            return true;
        } catch (SQLException e) {
            e.printStackTrace();
            LOGGER.error("执行修改异常:" + e.getMessage());
            throw new DataAccessException("数据更新异常");
        }
    }

    public boolean update(String sql, Object... params) throws DataAccessException {
        try {
            LOGGER.debug("执行修改的SQL==================>" + sql);
            LOGGER.debug("执行修改SQL参数================>" + ArrayUtils.toString(params));
            return new QueryRunner().update(JDBCUtil.getConnection(), sql, params)>0;
        } catch (SQLException e) {
            e.printStackTrace();
            LOGGER.error("执行修改异常:" + e.getMessage());
            throw new DataAccessException("数据更新异常");
        }
    }

    public boolean delete(String sql, Object... params) throws DataAccessException {
        try {
            LOGGER.debug("执行删除的SQL==================>" + sql);
            LOGGER.debug("执行删除SQL参数================>" + ArrayUtils.toString(params));
            return  new QueryRunner().update(JDBCUtil.getConnection(), sql, params)>0;
        } catch (SQLException e) {
            e.printStackTrace();
            LOGGER.error("执行删除异常:" + e.getMessage());
            throw new DataAccessException("数据删除异常");
        }
    }

    /**
     * 查询 返回多行数据
     *
     * @param sql
     * @param cls
     * @param params
     * @param <T>
     * @return
     * @throws DataAccessException
     */
    public <T> T queryForList(String sql, Class<T> cls, Object... params) throws DataAccessException {
        List list = new ArrayList() ;
        try {
            LOGGER.debug("执行查询queryForList的SQL==================>" + sql);
            LOGGER.debug("执行查询queryForListS 参数================>" + ArrayUtils.toString(params));
            List<Map<String, Object>> mapList = new QueryRunner().query(JDBCUtil.getConnection(), sql, new MapListHandler(), params);
            if (mapList==null || mapList.isEmpty()){
                return (T) list;
            }
            //循环list
            mapList.forEach(map->{
                try {
                    //通过反射创建对象
                    T pojoInstance = cls.newInstance();
                    // 使用map进行填充 属性 支持级联属性赋值
                    BeanUtils.populate(pojoInstance,map);
                    //添加到 list 集合中
                    list.add(pojoInstance) ;
                } catch (Exception e) {
                    e.printStackTrace();
                }
            });
            return (T) list;
        } catch (Exception e) {
            e.printStackTrace();
            LOGGER.error("执行查询queryForList异常:" + e.getMessage());
            throw new DataAccessException("数据查询异常");
        }
    }


	/**
	 * 查询返回 单行数据
	 * @param sql
	 * @param cls
	 * @param params
	 * @param <T>
	 * @return
	 * @throws DataAccessException
	 */
	public <T> T queryForObject(String sql, Class<T> cls, Object... params) throws DataAccessException {
		try {
			LOGGER.debug("执行查询queryForObject的SQL==================>" + sql);
			LOGGER.debug("执行查询queryForObject   参数================>" + ArrayUtils.toString(params));
            Map<String, Object> map = new QueryRunner().query(JDBCUtil.getConnection(), sql, new MapHandler(), params);
            if (map==null||map.isEmpty()){
                return null;
            }
            T pojo = cls.newInstance();
            BeanUtils.populate(pojo,map);
            return pojo;
        } catch (Exception e) {
			e.printStackTrace();
			LOGGER.error("queryForObject异常:" + e.getMessage());
			throw new DataAccessException("数据查询异常");
		}
	}


	/**
	 * 查询 满足条件的总记录数
	 * @param sql
	 * @param cls
	 * @param params
	 * @return
	 * @throws DataAccessException
	 */
	public Long queryForLong(String sql, Object... params) throws DataAccessException {
		try {
			LOGGER.debug("执行查询queryForInt的SQL==================>" + sql);
			LOGGER.debug("执行查询queryForInt   参数================>" + ArrayUtils.toString(params));
			return new QueryRunner().query(JDBCUtil.getConnection(), sql, new ScalarHandler<>(), params);
		} catch (Exception e) {
			e.printStackTrace();
			LOGGER.error("执行查询queryForInt异常:" + e.getMessage());
			throw new DataAccessException("数据查询异常");
		}
	}
}
